package test.testcase.dao;

import test.utils.JsonResult;
import test.utils.Mysql;

public class TimerDao {
	
	public static JsonResult getAnExcutableTime() {
		return Mysql.select("SELECT a.* FROM timer a \r\n" + 
				"WHERE\r\n" + 
				"(a.`excuteday` IS NULL OR a.`excuteday`<DATE_FORMAT(CURDATE(),'%Y-%m-%d'))\r\n" + 
				"AND a.`time`<=DATE_FORMAT(NOW(),'%H:%i:%s')\r\n" + 
				"AND LOCATE(DATE_FORMAT(CURDATE(),'%w'), a.`days`)>0 LIMIT 1;");
	}
	
	public static JsonResult setTimerExcuted(String timerId) {
		return Mysql.excute("update timer set excuteday=DATE_FORMAT(CURDATE(),'%Y-%m-%d') where id="+timerId);
	}
	
	public static JsonResult add(String userid, String title, String jobid, String projectid, String days, String time) {
		return Mysql.excute("insert into timer (userid,title,jobid,projectid,days,time,createtime) values("+userid+",'"+title+"',"+jobid+","+projectid+",'"+days+"','"+time+"',now())");
	}
	
	public static JsonResult save(String id, String title, String jobid, String projectid, String days, String time) {
		return Mysql.excute("update timer set title='"+title+"',jobid="+jobid+",projectid="+projectid+",days='"+days+"',time='"+time+"' where id="+id);
	}
	
	public static JsonResult del(String id) {
		return Mysql.excute("delete from timer where id="+id);
	}

	public static JsonResult getTimerList(String projectid) {
		return Mysql.select("SELECT p.*,c.`projectname` FROM (SELECT \r\n" + 
				"  a.id,a.`title`,c.username,\r\n" + 
				"  CONCAT('每周', a.`days`, ' ', TIME) AS times,\r\n" + 
				"  a.`excuteday`,\r\n" + 
				"  a.`createtime`,\r\n" + 
				"  (\r\n" + 
				"    CASE\r\n" + 
				"      WHEN a.projectid IS NULL \r\n" + 
				"      THEN b.projectid \r\n" + 
				"      ELSE a.projectid \r\n" + 
				"    END\r\n" + 
				"  ) AS projectid \r\n" + 
				"FROM\r\n" + 
				"  timer a \r\n" + 
				"  LEFT JOIN job b \r\n" + 
				"    ON a.`jobid` = b.`id` left join user c on a.userid=c.id \r\n" + 
				"WHERE a.`projectid`="+projectid+" \r\n" + 
				"  OR a.`jobid` IN \r\n" + 
				"  (SELECT \r\n" + 
				"    id AS jobid \r\n" + 
				"  FROM\r\n" + 
				"    job \r\n" + 
				"  WHERE projectid = "+projectid+"))p LEFT JOIN project c ON p.projectid=c.id");
	}
	
	public static JsonResult getInfo(String id) {
		return Mysql.select("SELECT \r\n" + 
				"  a.`title`,\r\n" + 
				"  a.`days`,a.`time`,\r\n" + 
				"  a.`excuteday`,\r\n" + 
				"  a.`jobid`,\r\n" + 
				"  a.`createtime`,\r\n" + 
				"  (\r\n" + 
				"    CASE\r\n" + 
				"      WHEN a.projectid IS NULL \r\n" + 
				"      THEN b.projectid \r\n" + 
				"      ELSE a.projectid \r\n" + 
				"    END\r\n" + 
				"  ) AS projectid \r\n" + 
				"FROM\r\n" + 
				"  timer a \r\n" + 
				"  LEFT JOIN job b \r\n" + 
				"    ON a.`jobid` = b.`id` \r\n" + 
				"WHERE \r\n" + 
				"a.`id`="+id);
	}
}
